﻿<%@ WebHandler Language="C#" Class="ApsProducePrepaHandler" %>

using System;
using System.Web;
using BusinessRule.DTO;
using System.Web.SessionState;
using DatabaseAccess.DBUtility;
using System.Data.OracleClient;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Collections;
using Newtonsoft.Json;
using BusinessRule.Business;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;

//Description:排产生产准备管控处理程序
//Copyright (c) : 通力凯顿（北京）系统集成有限公司
//Writer:longb
//create Date:2023-03-24

public class ApsProducePrepaHandler : IHttpHandler, IRequiresSessionState
{
    CurrentUserInfo m_UserInfo;
    uLMSCommonBusiness commonBusiness = new uLMSCommonBusiness();
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            switch (action)
            {
                case "InitPageData": //获取页面初始化数据
                    InitPageData(context);
                    break;
                case "getApsProducePrepData": //查询生产准备任务信息
                    getApsProducePrepData(context, "");
                    break;
                case "getApsProducePrepDetailData": //查询生产准备--明细信息
                    getApsProducePrepDetailData(context);
                    break;
                case "statusFreshen"://状态刷新
                    statusFreshen(context);
                    break;
                case "saveDispatchEdit": //批量编辑--保存
                    saveDispatchEdit(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }


    /// <summary>
    /// 获取页面初始化数据
    /// </summary>
    /// <param name="context"></param>
    public void InitPageData(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult();
        var wcSQL = @"SELECT WC.WORKCENTERNAME, WC.WORKCENTERID FROM WORKCENTER WC
                    WHERE WC.FACTORYID = '{0}' AND (notes is null or notes <>'已注销')
                    ";
        var dt = DatabaseHelper.Query_ReturnDatTable(string.Format(wcSQL, m_UserInfo.FactoryID));
        result.DBTable = dt;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 查询生产准备任务信息
    /// </summary>
    /// <param name="context"></param>
    public void getApsProducePrepData(HttpContext context, string ContainerIDS)
    {
        var dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["data"].ToString());
        string pageSize = context.Request["pageSize"].ToString();
        string pageNumber = context.Request["pageNumber"].ToString();
        uMESProcessResult result = new uMESProcessResult();
        var selectSQl = selectSQL + " and CCI.OriginalFactoryID='" + m_UserInfo.FactoryID + "' ";
        if (!string.IsNullOrWhiteSpace(dic["workcenter"])) { selectSQl += " and CCI.WORKCENTERID IN('" + dic["workcenter"].Replace(",", "','") + "')"; }
        if (!string.IsNullOrWhiteSpace(dic["ProductName"])) { selectSQl += " and CCI.PRODUCTNAME like '%" + dic["ProductName"] + "%'"; }
        if (!string.IsNullOrWhiteSpace(dic["ContainerNo"])) { selectSQl += " and CCI.CONTAINERNAME  like '%" + dic["ContainerNo"] + "%'"; }
        if (!string.IsNullOrWhiteSpace(dic["MfgOrderName"])) { selectSQl += " and M.MfgOrderName like '%" + dic["MfgOrderName"] + "%'"; }
        if (!string.IsNullOrWhiteSpace(dic["ContainerStatus"])) { selectSQl += " and CCI.ContainerStatus='" + dic["ContainerStatus"] + "'"; }
        if (!string.IsNullOrWhiteSpace(dic["MfgOrderStartTime"])) { selectSQl += " and M.PlannedStartDate>=to_date('" + dic["MfgOrderStartTime"] + "','yyyy-MM-dd')"; }
        if (!string.IsNullOrWhiteSpace(dic["MfgOrderFinshTime"])) { selectSQl += " and M.PlannedCompletionDate<=to_date('" + dic["MfgOrderFinshTime"] + "','yyyy-MM-dd')"; }
        if (!string.IsNullOrWhiteSpace(dic["ContainerStartTime"])) { selectSQl += " and CCI.PLANNEDSTARTDATE>=to_date('" + dic["ContainerStartTime"] + "','yyyy-MM-dd')"; }
        if (!string.IsNullOrWhiteSpace(dic["ContainerFinshTime"])) { selectSQl += " and CCI.PLANNEDCOMPLETIONDATE<=to_date('" + dic["ContainerFinshTime"] + "','yyyy-MM-dd')"; }
        if (!string.IsNullOrWhiteSpace(dic["IsComp"])) { selectSQl += " and decode(CCI.ApsProPre,NULL,0,CCI.ApsProPre)='" + dic["IsComp"] + "'"; }
        if (!string.IsNullOrWhiteSpace(dic["IsHold"])) { selectSQl += " and nvl(CCI.IsHold,0)='" + dic["IsHold"] + "'"; }
        
        selectSQl += " order by ApsSequence asc, pc.prioritycodename ";
        var paraPage = new uMESPagingDataDTO();
        paraPage.CurrentPageIndex = Convert.ToInt32(pageNumber);
        paraPage.PageSize = Convert.ToInt32(pageSize);
        paraPage.strSQL = selectSQl;
        result = DatabaseHelper.RunProcedureForPage(paraPage);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 查询生产准备--明细信息
    /// </summary>
    public void getApsProducePrepDetailData(HttpContext context)
    {
        var ContainerID = context.Request["ContainerID"].ToString();
        var workflowid = context.Request["workflowid"].ToString(); 
        var result = new uMESProcessResult();
        DataSet set = new DataSet();
        int IsComp = 0;
        //原材料数据 
        DataTable Material = getMaterialData(ContainerID, ref IsComp);
        Material.TableName = "MaterialTable";
        set.Tables.Add(Material);
        //集件数据 
        DataTable Issue = getIssueData(ContainerID,workflowid, ref IsComp);
        Issue.TableName = "IssueTable";
        set.Tables.Add(Issue);
        //夹模具数据 
        DataTable Special = getToolData(ContainerID, workflowid,"special", ref IsComp);
        Special.TableName = "SpecialTable";
        set.Tables.Add(Special);
        //刀具数据 
        DataTable Cut = getToolData(ContainerID, workflowid,"cut", ref IsComp);
        Cut.TableName = "CutTable";
        set.Tables.Add(Cut);
        //量具数据 
        DataTable Measure = getToolData(ContainerID, workflowid,"measure", ref IsComp);
        Measure.TableName = "MeasureTable";
        set.Tables.Add(Measure);
        //辅材数据 
        DataTable AuxMaterial = getAuxMaterialData(ContainerID, workflowid,ref IsComp);
        AuxMaterial.TableName = "AuxMaterialTable";
        set.Tables.Add(AuxMaterial);
        //数控程序数据 
        DataTable Control = getControlData(ContainerID, ref IsComp);
        Control.TableName = "ControlTable";
        set.Tables.Add(Control);
        //特殊过程数据
        DataTable Spotcheck = getSpotcheckData(ContainerID, ref IsComp);
        Spotcheck.TableName = "SpotcheckTable";
        set.Tables.Add(Spotcheck);
        result.DBDataSet = set;
        result.Result = true;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 原材料数据
    /// </summary>
    /// <param name="ContainerID"></param>
    /// <returns></returns>
    public DataTable getMaterialData(string ContainerID, ref Int32 IsComp)
    {
        var selectSQL = @"SELECT c.MFGORDERNAME,p.MaterialTrademark,nvl(td.LABST_Q,0) LABSTQ,t.ATTRIBUTEVALUE SFS,t1.ATTRIBUTEVALUE JYBH,t2.ATTRIBUTEVALUE RLH
                             ,decode(nvl(td.LABST_Q,0),0,'是','否') ISCOMP
                             FROM CONTAINERCURRENTINFO c 
                             LEFT JOIN Userattribute t ON t.PARENTID =c.CONTAINERID AND t.USERATTRIBUTENAME ='实发数/顺序号'
                             LEFT JOIN Userattribute t1 ON t1.PARENTID =c.CONTAINERID AND t1.USERATTRIBUTENAME ='检验编号'
                             LEFT JOIN Userattribute t2 ON t2.PARENTID =c.CONTAINERID AND t2.USERATTRIBUTENAME ='熔炉号母'
                             LEFT JOIN PRODUCT p ON p.PRODUCTID =c.PRODUCTID 
                             LEFT JOIN T_DDQLT td ON td.AUFNR =c.MFGORDERNAME
                             WHERE c.CONTAINERID ='{0}'";
        DataTable Material= DatabaseHelper.Query_ReturnDatTable(string.Format(selectSQL, ContainerID));
        DataView dv = Material.DefaultView;
        dv.RowFilter = "ISCOMP='否'";
        if (dv.ToTable().Rows.Count > 0) { IsComp = 0; }
        return Material;
    }

    /// <summary>
    /// 集件数据 
    /// </summary>
    /// <param name="ContainerID"></param>
    /// <returns></returns>
    public DataTable getIssueData(string ContainerID,string workflowid, ref Int32 IsComp)
    {
        var selectSQL = @"select ai.ApsType,ai.containerid,cci.containername,ai.workflowid,ad.workflowstepid,ad.workflowstepname,s.SPECNO ,--decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME) SPECNAME,
                         DECODE(decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME),NULL,SUBSTR(ad.workflowstepname,INSTR(ad.workflowstepname,'-',-1,1)+1),decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME)) SPECNAME, 
                         to_char(ad.starttime,'yyyy-MM-dd hh24:mi:ss') stepstarttime, to_char(ad.endtime,'yyyy-MM-dd hh24:mi:ss') stependtime,T.PRODUCTNAME,T.QTYREQUIRED
                          ,nvl(td.LABSTQ,0) LABSTQ,decode(nvl(td.LABSTQ,0),0,'是','否') ISCOMP
                          from APSPLANINFO ai 
                          left join containercurrentinfo cci on cci.containerid=ai.containerid
                          left join ApsPlanDetail ad on ad.apsplaninfoid=ai.apsplaninfoid and ad.isuse=0
                          LEFT JOIN WORKFLOWSTEP WFS ON WFS.WORKFLOWSTEPID =ad.WORKFLOWSTEPID 
                          LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
                          LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
                          LEFT JOIN (
                                SELECT PBN.PRODUCTNAME, PML.QTYREQUIRED,NVL(SB.REVOFRCDID, PML.SPECID) SPECID
                                 FROM PRODUCTMATERIALLISTITEM PML
                                 LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = PML.PRODUCTBASEID
                                 LEFT JOIN PRODUCT P ON P.PRODUCTID =NVL(PB.REVOFRCDID, PML.PRODUCTID)
                                 LEFT JOIN PRODUCTBASE PBN ON PBN.PRODUCTBASEID=P.PRODUCTBASEID
                                 LEFT JOIN SPECBASE SB ON SB.SPECBASEID=PML.SPECBASEID
                                 LEFT JOIN WORKFLOWSTEP ws ON ws.specid=NVL(SB.REVOFRCDID, PML.SPECID)                                 
                                 WHERE ws.workflowid='{0}'
                          )T ON T.SPECID=S.SPECID
                          left join (SELECT nvl(SUM(td.LABST_Q),0) LABSTQ,td.AUFNR,td.IDNRK FROM T_DDQLT td GROUP BY td.AUFNR,td.IDNRK) td ON td. AUFNR=cci.MFGORDERNAME and  t.PRODUCTNAME= td.IDNRK                       
                          where ai.isuse=0 and ai.ApsType=1 and ai.ApsType2=2 and nvl(T.QTYREQUIRED,0)>0 AND ai.containerid='{1}' --正排--有限产能--
                          order by  s.SPECNO";
        DataTable Issue = DatabaseHelper.Query_ReturnDatTable(string.Format(selectSQL, workflowid,ContainerID));
        DataView dv = Issue.DefaultView;
        dv.RowFilter = "ISCOMP='否'";
        if (dv.ToTable().Rows.Count > 0) { IsComp = 0; }
        return Issue;
    }

    /// <summary>
    /// 辅材数据 
    /// </summary>
    /// <param name="ContainerID"></param>
    /// <returns></returns>
    public DataTable getAuxMaterialData(string ContainerID,string workflowid, ref Int32 IsComp)
    {
        var selectSQL = @"select ai.ApsType,ai.containerid,cci.containername,ai.workflowid,ad.workflowstepid,ad.workflowstepname,s.SPECNO ,--decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME) SPECNAME,
                          DECODE(decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME),NULL,SUBSTR(ad.workflowstepname,INSTR(ad.workflowstepname,'-',-1,1)+1),decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME)) SPECNAME,
                          to_char(ad.starttime,'yyyy-MM-dd hh24:mi:ss') stepstarttime, to_char(ad.endtime,'yyyy-MM-dd hh24:mi:ss') stependtime,T.AUXMATERIALNAME
                          ,'是' ISCOMP
                          from APSPLANINFO ai 
                          left join containercurrentinfo cci on cci.containerid=ai.containerid
                          left join ApsPlanDetail ad on ad.apsplaninfoid=ai.apsplaninfoid and ad.isuse=0
                          LEFT JOIN WORKFLOWSTEP WFS ON WFS.WORKFLOWSTEPID =ad.WORKFLOWSTEPID 
                          LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
                          LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
                          LEFT JOIN ( SELECT SUM(QTY)QTY,am.AUXMATERIALNAME,T.WORKFLOWSTEPID FROM (
	                        	           SELECT T.QTY, T.SEQUENCE,T.TOOLS,T.WORKFLOWSTEPID FROM WORKFLOWSTEPTOOLS T
                                           LEFT JOIN workflowstep ws ON ws.workflowstepid=t.workflowstepid 
                                           WHERE ws.workflowid='{0}'
								           UNION 
							               SELECT T.QTY, T.SEQUENCE,T.TOOLS,SIO.WORKFLOWSTEPID FROM STEPINFOTOOLS T
								           LEFT JOIN STEPINFO SIO ON SIO.STEPINFOID = T.STEPINFOID
                                           LEFT JOIN workflowstep ws ON ws.workflowstepid=SIO.workflowstepid 
                                            WHERE ws.workflowid='{0}'
							           ) T 
							          LEFT JOIN auxmaterial am on am.auxmaterialid=T.tools WHERE am.auxmaterialid IS NOT NULL
						              GROUP BY am.AUXMATERIALNAME,T.WORKFLOWSTEPID
                          )T ON T.WORKFLOWSTEPID=ad.WORKFLOWSTEPID 
                          where ai.isuse=0 and ai.ApsType=1 and ai.ApsType2=2 and T.AUXMATERIALNAME is not null AND ai.containerid='{1}' --正排--有限产能--
                          order by  s.SPECNO";
        DataTable AuxMaterial = DatabaseHelper.Query_ReturnDatTable(string.Format(selectSQL,workflowid, ContainerID));
        DataView dv = AuxMaterial.DefaultView;
        dv.RowFilter = "ISCOMP='否'";
        if (dv.ToTable().Rows.Count > 0) { IsComp = 0; }
        return AuxMaterial;
    }

    /// <summary>
    /// 查询-排产工序-工装工具数据
    /// </summary>
    /// <param name="ContainerID"></param>
    /// <param name="type"></param>
    /// <param name="?"></param>
    /// <param name="IsComp"></param>
    /// <returns></returns>
    public DataTable getToolData(string ContainerID, string workflowid,string type, ref int IsComp)
    {
        var table = "SELECT CUTTOOLNAME TOOLNAME,DESCRIPTION,CUTTOOLID TOOLID,RESOURCETYPEID,DRAWINGNO FROM  CUTTOOL";
        if (type == "measure") { table = "SELECT MEASURETOOLNAME TOOLNAME,DESCRIPTION,MEASURETOOLID TOOLID,RESOURCETYPEID,DRAWINGNO FROM  MEASURETOOL"; }
        if (type == "special") { table = "SELECT SPECIALTOOLNAME TOOLNAME,DESCRIPTION,SPECIALTOOLID TOOLID,RESOURCETYPEID,DRAWINGNO FROM  SPECIALTOOL"; }
        var selectSQL = @"select ai.ApsType,ai.containerid,cci.containername,ai.workflowid,ad.workflowstepid,ad.workflowstepname,s.SPECNO ,--decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME) SPECNAME,
                          DECODE(decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME),NULL,SUBSTR(ad.workflowstepname,INSTR(ad.workflowstepname,'-',-1,1)+1),decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME)) SPECNAME,
                          to_char(ad.starttime,'yyyy-MM-dd hh24:mi:ss') stepstarttime, to_char(ad.endtime,'yyyy-MM-dd hh24:mi:ss') stependtime
                          ,T.QTY,T.TOOLNAME,T.DESCRIPTION,T.RESOURCETYPENAME,nvl(tg.MENGE,0) MENGE,
                           case when nvl(tg.MENGE,0)>=nvl(T.QTY,0) then '是' else '否' end ISCOMP
                          from APSPLANINFO ai 
                          left join containercurrentinfo cci on cci.containerid=ai.containerid
                          left join ApsPlanDetail ad on ad.apsplaninfoid=ai.apsplaninfoid and ad.isuse=0
                          LEFT JOIN WORKFLOWSTEP WFS ON WFS.WORKFLOWSTEPID =ad.WORKFLOWSTEPID 
                          LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
                          LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
                          LEFT JOIN (
                          	 SELECT SUM(QTY) QTY,CT.TOOLNAME,CT.DESCRIPTION,DRAWINGNO,RT.RESOURCETYPENAME,T.WORKFLOWSTEPID FROM (
	                          	SELECT T.QTY, T.SEQUENCE,T.TOOLS,T.WORKFLOWSTEPID FROM WORKFLOWSTEPTOOLS T
                                LEFT JOIN workflowstep ws ON ws.workflowstepid=t.workflowstepid 
                                WHERE ws.workflowid='{0}'
								UNION 
							    SELECT T.QTY, T.SEQUENCE,T.TOOLS,SIO.WORKFLOWSTEPID FROM STEPINFOTOOLS T
								LEFT JOIN STEPINFO SIO ON SIO.STEPINFOID = T.STEPINFOID
                                LEFT JOIN workflowstep ws ON ws.workflowstepid=SIO.workflowstepid 
                                WHERE ws.workflowid='{0}'
							) T 
							 LEFT JOIN ({1}) CT  ON CT.TOOLID = T.TOOLS
						 	 LEFT JOIN RESOURCETYPE RT  ON RT.RESOURCETYPEID = CT.RESOURCETYPEID WHERE CT.TOOLID IS NOT NULL 
						 	 GROUP BY CT.TOOLNAME,CT.DESCRIPTION,CT.DRAWINGNO,RT.RESOURCETYPENAME,T.WORKFLOWSTEPID
                          ) T ON T.WORKFLOWSTEPID=ad.WORKFLOWSTEPID
                         LEFT JOIN(SELECT SUM(tg.MENGE) MENGE,tg.ZEINR FROM T_GGDKC tg GROUP BY tg.ZEINR) tg on tg.ZEINR=DRAWINGNO                      
                          where ai.isuse=0 and ai.ApsType=1 and ai.ApsType2=2 and nvl(T.QTY,0)>0 AND ai.containerid='{2}' --正排--有限产能--
                          order by  s.SPECNO";
        DataTable Tool = DatabaseHelper.Query_ReturnDatTable(string.Format(selectSQL, workflowid,table, ContainerID));
        DataView dv = Tool.DefaultView;
        dv.RowFilter = "ISCOMP='否'";
        if (dv.ToTable().Rows.Count > 0) { IsComp = 0; }
        return Tool;
    }

    /// <summary>
    /// 查询-排产工序-数控程序数据
    /// </summary>
    /// <param name="ContainerID"></param>
    /// <returns></returns>
    public DataTable getControlData(string ContainerID, ref int IsComp)
    {
        var selectSQL = @"select ai.ApsType,ai.containerid,cci.containername,ai.workflowid,ad.workflowstepid,ad.workflowstepname,s.SPECNO ,--decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME) SPECNAME,
                          DECODE(decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME),NULL,SUBSTR(ad.workflowstepname,INSTR(ad.workflowstepname,'-',-1,1)+1),decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME)) SPECNAME,     
                          to_char(ad.starttime,'yyyy-MM-dd hh24:mi:ss') stepstarttime, to_char(ad.endtime,'yyyy-MM-dd hh24:mi:ss') stependtime,
                          decode(s.NCNUMBER,1,'是','否') NCNUMBER,''NCNUMBERNAME,'否'ISCOMP
                          from APSPLANINFO ai 
                          left join containercurrentinfo cci on cci.containerid=ai.containerid
                          left join ApsPlanDetail ad on ad.apsplaninfoid=ai.apsplaninfoid and ad.isuse=0
                          LEFT JOIN WORKFLOWSTEP WFS ON WFS.WORKFLOWSTEPID =ad.WORKFLOWSTEPID 
                          LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
                          LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
                          where ai.isuse=0 and ai.ApsType=1 and ai.ApsType2=2 and nvl(s.NCNUMBER,0)=1 AND ai.containerid='{0}' --正排--有限产能--
                          order by  s.SPECNO";
        DataTable Control = DatabaseHelper.Query_ReturnDatTable(string.Format(selectSQL, ContainerID));
        DataView dv = Control.DefaultView;
        dv.RowFilter = "ISCOMP='否'";
        if (dv.ToTable().Rows.Count > 0) { IsComp = 0; }
        return Control;
    }

    /// <summary>
    /// 查询-排产工序-特殊过程数据
    /// </summary>
    /// <param name="ContainerID"></param>
    /// <returns></returns>
    public DataTable getSpotcheckData(string ContainerID, ref int IsComp)
    {
        var selectSQL = @" select ai.ApsType,ai.containerid,cci.containername,ai.workflowid,ad.workflowstepid,ad.workflowstepname,s.SPECNO ,--decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME) SPECNAME,
                            DECODE(decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME),NULL,SUBSTR(ad.workflowstepname,INSTR(ad.workflowstepname,'-',-1,1)+1),decode(sb.SPECNAME,NULL,s.description,sb.SPECNAME)) SPECNAME,
                            to_char(ad.starttime,'yyyy-MM-dd hh24:mi:ss') stepstarttime, to_char(ad.endtime,'yyyy-MM-dd hh24:mi:ss') stependtime,SR.SPECIALCOURSESPECREPORTNO , 
                            to_char(SR.INDATE,'yyyy-MM-dd hh24:mi:ss') INDATE,s.isspotcheck,CASE WHEN SR.INDATE IS NOT NULL AND SR.INDATE>ad.endtime THEN '是' ELSE '否' END ISCOMP
                          from APSPLANINFO ai 
                          left join containercurrentinfo cci on cci.containerid=ai.containerid
                          left join ApsPlanDetail ad on ad.apsplaninfoid=ai.apsplaninfoid and ad.isuse=0
                          LEFT JOIN WORKFLOWSTEP WFS ON WFS.WORKFLOWSTEPID =ad.WORKFLOWSTEPID 
                          LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
                          LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
                          LEFT JOIN SpecialCourseSpecMapp SC ON SC.WORKFLOWSTEPID =ad.WORKFLOWSTEPID 
                          LEFT JOIN SpecialCourseSpecReport SR ON SR.SPECIALCOURSESPECREPORTID =SC.SPECIALCOURSESPECREPORTID 
                          where ai.isuse=0 and ai.ApsType=1 and ai.ApsType2=2  AND ai.containerid='{0}' and s.isspotcheck=1--正排--有限产能--
                          order by  s.SPECNO";
        DataTable Spotcheck = DatabaseHelper.Query_ReturnDatTable(string.Format(selectSQL, ContainerID));
        DataView dv = Spotcheck.DefaultView;
        dv.RowFilter = "ISCOMP='否'";
        if (dv.ToTable().Rows.Count > 0) { IsComp = 0; }
        return Spotcheck;
    }

    /// <summary>
    /// 状态刷新
    /// </summary>
    /// <param name="context"></param>
    public void statusFreshen(HttpContext context)
    {
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["mdata"].ToString()); 
        foreach (DataRow row in dt.Rows)
        {
            var ContainerID = row["CONTAINERID"].ToString();
            var workflowid = row["workflowid"].ToString();
            //原材料是否齐套
            if (row["ISMaterial"].ToString().Equals("0"))
            {
                var ISMaterial = 1;
                getMaterialData(ContainerID, ref ISMaterial);
                row["ISMaterial"] = ISMaterial;
            }
            //集件是否齐套
            if (row["ISIssue"].ToString().Equals("0"))
            {
                var ISIssue = 1;
                getIssueData(ContainerID,workflowid, ref ISIssue);
                row["ISIssue"] = ISIssue;
            }
            //夹模具是否齐套
            if (row["ISSpecial"].ToString().Equals("0"))
            {
                var ISSpecial = 1;
                getToolData(ContainerID,workflowid, "special", ref ISSpecial);
                row["ISSpecial"] = ISSpecial;
            }
            //刀具是否齐套
            if (row["ISCut"].ToString().Equals("0"))
            {
                var ISCut = 1;
                getToolData(ContainerID,workflowid, "cut", ref ISCut);
                row["ISCut"] = ISCut;
            }
            //量具是否齐套
            if (row["ISMeasure"].ToString().Equals("0"))
            {
                var ISMeasure = 1;
                getToolData(ContainerID, workflowid,"measure", ref ISMeasure);
                row["ISMeasure"] = ISMeasure;
            }
            //辅材是否齐套
            if (row["ISAuxMaterial"].ToString().Equals("0"))
            {
                var ISAuxMaterial = 1;
                getAuxMaterialData(ContainerID,workflowid, ref ISAuxMaterial);
                row["ISAuxMaterial"] = ISAuxMaterial;
            }
            //数控程序是否齐套
            if (row["ISControl"].ToString().Equals("0"))
            {
                var ISControl = 1;
                getControlData(ContainerID, ref ISControl);
                row["ISControl"] = ISControl;
            }
            //特殊过程是否齐套
            if (row["ISSpotcheck"].ToString().Equals("0"))
            {
                var ISSpotcheck = 1;
                getSpotcheckData(ContainerID, ref ISSpotcheck);
                row["ISSpotcheck"] = ISSpotcheck;
            }
        }
        var arrList = new ArrayList();
        foreach (DataRow row in dt.Rows) {
            if (row["MARKTYPE"].ToString().Equals("1"))
            {//状态刷新--针对批次标记为手动【0】的，不做批次齐套更新
                if (row["ISMaterial"].ToString().Equals("1") && row["ISIssue"].ToString().Equals("1") && row["ISSpecial"].ToString().Equals("1") && row["ISCut"].ToString().Equals("1") &&
                    row["ISMeasure"].ToString().Equals("1") && row["ISAuxMaterial"].ToString().Equals("1") && row["ISControl"].ToString().Equals("1") && row["ISSpotcheck"].ToString().Equals("1"))
                {
                    arrList.Add("update CONTAINERCURRENTINFO CCI set CCI.ApsProPre=1 where CCi.CONTAINERID='" + row["CONTAINERID"] + "'");
                } else 
                {
                    arrList.Add("update CONTAINERCURRENTINFO CCI set CCI.ApsProPre=0 where CCi.CONTAINERID='" + row["CONTAINERID"] + "'");
                }
            }
            if (string.IsNullOrWhiteSpace(row["APSCONCOMPLETESETID"].ToString())) 
            {
                var insertSQL = @"INSERT INTO ApsConCompleteSet(APSCONCOMPLETESETID,CONTAINERID,CREATEDATE,CREATEEMPLOYEEID,ISUSE,MARKTYPE,ISCOMPMATERIAL,ISCOMPISSUE,
                            ISCOMPSPECIAL,ISCOMPCUT,ISCOMPMEASURE,ISCOMPAUXMATERIAL,ISCOMPCONTROL,ISCOMPSPOTCHECK)
                            values(sys_guid(),'{0}',sysdate,'{1}',0,1,'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')";
                arrList.Add(string.Format(insertSQL, row["CONTAINERID"], m_UserInfo.EmployeeID.ToString(), row["ISMaterial"], row["ISIssue"], row["ISSpecial"], row["ISCut"], row["ISMeasure"], row["ISAuxMaterial"], row["ISControl"], row["ISSpotcheck"]));
            } 
            else {
                var updateSQL = @"UPDATE ApsConCompleteSet SET MARKTYPE='{0}', ISCOMPMATERIAL='{1}', ISCOMPISSUE='{2}', ISCOMPSPECIAL='{3}', ISCOMPCUT='{4}', 
                                 ISCOMPMEASURE='{5}', ISCOMPAUXMATERIAL='{6}', ISCOMPCONTROL='{7}', ISCOMPSPOTCHECK='{8}' where CONTAINERID='{9}'";
                arrList.Add(string.Format(updateSQL, "1", row["ISMaterial"], row["ISIssue"], row["ISSpecial"], row["ISCut"], row["ISMeasure"], row["ISAuxMaterial"], row["ISControl"], row["ISSpotcheck"], row["CONTAINERID"]));
            }
       }
        var result = new uMESProcessResult();
        result.Result = DatabaseHelper.ExecuteSqlListTran(arrList);
        result.Message = result.Result ? "状态刷新成功" : "状态刷新失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 批量编辑--保存
    /// </summary>
    /// <param name="context"></param>
    public void saveDispatchEdit(HttpContext context)
    {
        var dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["data"].ToString());
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["mdata"].ToString()); 
        foreach (DataRow row in dt.Rows)
        {
            var ContainerID = row["CONTAINERID"].ToString();
            var workflowid = row["workflowid"].ToString();
            //原材料是否齐套
            if (row["ISMaterial"].ToString().Equals("0"))
            {
                var ISMaterial = 1;
                getMaterialData(ContainerID, ref ISMaterial);
                row["ISMaterial"] = ISMaterial;
            }
            //集件是否齐套
            if (row["ISIssue"].ToString().Equals("0"))
            {
                var ISIssue = 1;
                getIssueData(ContainerID,workflowid, ref ISIssue);
                row["ISIssue"] = ISIssue;
            }
            //夹模具是否齐套
            if (row["ISSpecial"].ToString().Equals("0"))
            {
                var ISSpecial = 1;
                getToolData(ContainerID, workflowid,"special", ref ISSpecial);
                row["ISSpecial"] = ISSpecial;
            }
            //刀具是否齐套
            if (row["ISCut"].ToString().Equals("0"))
            {
                var ISCut = 1;
                getToolData(ContainerID, workflowid,"cut", ref ISCut);
                row["ISCut"] = ISCut;
            }
            //量具是否齐套
            if (row["ISMeasure"].ToString().Equals("0"))
            {
                var ISMeasure = 1;
                getToolData(ContainerID,workflowid, "measure", ref ISMeasure);
                row["ISMeasure"] = ISMeasure;
            }
            //辅材是否齐套
            if (row["ISAuxMaterial"].ToString().Equals("0"))
            {
                var ISAuxMaterial = 1;
                getAuxMaterialData(ContainerID, workflowid,ref ISAuxMaterial);
                row["ISAuxMaterial"] = ISAuxMaterial;
            }
            //数控程序是否齐套
            if (row["ISControl"].ToString().Equals("0"))
            {
                var ISControl = 1;
                getControlData(ContainerID, ref ISControl);
                row["ISControl"] = ISControl;
            }
            //特殊过程是否齐套
            if (row["ISSpotcheck"].ToString().Equals("0"))
            {
                var ISSpotcheck = 1;
                getSpotcheckData(ContainerID, ref ISSpotcheck);
                row["ISSpotcheck"] = ISSpotcheck;
            }
        }
        
        var arrList = new ArrayList();
        foreach (DataRow row in dt.Rows) {
            //if (row["MARKTYPE"].ToString().Equals("1"))
            //{
            //    arrList.Add(string.Format("update CONTAINERCURRENTINFO CCI set CCI.ApsProPre='{0}' where CCi.CONTAINERID='{1}'", dic["IsComp"], row["CONTAINERID"]));
            //}
            arrList.Add(string.Format("update CONTAINERCURRENTINFO CCI set CCI.ApsProPre='{0}' where CCi.CONTAINERID='{1}'", dic["IsComp"], row["CONTAINERID"]));
            if (string.IsNullOrWhiteSpace(row["APSCONCOMPLETESETID"].ToString())) {
                var insertSQL = @"INSERT INTO ApsConCompleteSet(APSCONCOMPLETESETID,CONTAINERID,CREATEDATE,CREATEEMPLOYEEID,ISUSE,MARKTYPE,ISCOMPMATERIAL,ISCOMPISSUE,
                            ISCOMPSPECIAL,ISCOMPCUT,ISCOMPMEASURE,ISCOMPAUXMATERIAL,ISCOMPCONTROL,ISCOMPSPOTCHECK)
                            values(sys_guid(),'{0}',sysdate,'{1}',0,'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')";
                arrList.Add(string.Format(insertSQL, row["CONTAINERID"], m_UserInfo.EmployeeID.ToString(), dic["MarkMethod"], row["ISMaterial"], row["ISIssue"], row["ISSpecial"], row["ISCut"], row["ISMeasure"], row["ISAuxMaterial"], row["ISControl"], row["ISSpotcheck"]));
            } else {
                var updateSQL = @"UPDATE ApsConCompleteSet SET MARKTYPE='{0}', ISCOMPMATERIAL='{1}', ISCOMPISSUE='{2}', ISCOMPSPECIAL='{3}', ISCOMPCUT='{4}', 
                                 ISCOMPMEASURE='{5}', ISCOMPAUXMATERIAL='{6}', ISCOMPCONTROL='{7}', ISCOMPSPOTCHECK='{8}' where CONTAINERID='{9}'";
                arrList.Add(string.Format(updateSQL,dic["MarkMethod"], row["ISMaterial"], row["ISIssue"], row["ISSpecial"], row["ISCut"], row["ISMeasure"], row["ISAuxMaterial"], row["ISControl"], row["ISSpotcheck"], row["CONTAINERID"]));
            }
         }
        var result = new uMESProcessResult();
        result.Result = DatabaseHelper.ExecuteSqlListTran(arrList);
        result.Message = result.Result ? "批量操作成功" : "批量操作失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    public static string selectSQL = @"SELECT distinct CCI.MFGORDERNAME,CCI.CONTAINERID,CCI.CONTAINERNAME,CCI.PRODUCTDESCRIPTION PRODUCTDESC,CCI.PRODUCTREVISION,CCI.QTY,CCI.WORKFLOWSTEPNAME,
                CCI.CONTAINERTYPE,DECODE(CCI.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
                W.WORKFLOWID,WB.WORKFLOWNAME,W.WORKFLOWREVISION,WB.WORKFLOWNAME||':'||W.WORKFLOWREVISION WORKFLOWINFO,--wc.apspoolid,
                TO_CHAR(M.PLANNEDSTARTDATE,'YYYY-MM-DD') MFGSTARTDATE,TO_CHAR(M.PLANNEDCOMPLETIONDATE,'YYYY-MM-DD') MFGENDDDATE,to_char(cci.ExpectMaterialDate,'yyyy-mm-dd hh24:mi:ss') ExpectMaterialDate,
                to_char(cci.materialdate,'yyyy-mm-dd hh24:mi:ss') materialdate ,TO_CHAR(M.PLANNEDSTARTDATE,'YYYY-MM-DD') REALSTARTDATE, 
                nvl2(cc.containersubcontainerid,'1','') ISSUBCON,cci.togetherflag,pc.prioritycodename,cci.CriticalValue,cci.StepDuration,cci.ApsSequence,CCI.ContainerStatus
                ,TO_CHAR(CCI.PLANNEDSTARTDATE,'YYYY-MM-DD') STARTTIME,2 DJDKSPECIAL,TO_CHAR(CCI.PLANNEDCOMPLETIONDATE,'YYYY-MM-DD') ENDTIME,CCI.FamilyListNames
                ,decode(CCI.ApsProPre,NULL,0,CCI.ApsProPre) IsComp,acc.APSCONCOMPLETESETID,nvl(acc.ISCOMPMaterial,0) ISMaterial,nvl(acc.ISCompIssue,0) ISIssue,nvl(acc.ISCompSpecial,0) ISSpecial,nvl(acc.ISCompCut,0) ISCut,
                nvl(acc.ISCompMeasure,0) ISMeasure,nvl(acc.ISCompAuxMaterial,0) ISAuxMaterial,nvl(acc.ISCompControl,0) ISControl,nvl(acc.ISCompSpotcheck,0) ISSpotcheck,nvl(acc.MARKTYPE,1) MARKTYPE
                FROM CONTAINERCURRENTINFO CCI
                left join apspoolworkcenter wc on cci.fromworkcenterid=wc.workcenterid
                left join apspooljhyemp ae on ae.apspoolid=wc.apspoolid 
                left join apspool ap on ae.apspoolid=ap.apspoolid and ap.isuse=0     
                LEFT JOIN MFGORDER M ON M.MFGORDERID=CCI.MFGORDERID
                left join prioritycode pc on pc.prioritycodeid=m.priorityid
                LEFT JOIN ORDERSTATUS o ON o.orderstatusid = M.orderstatusid
                LEFT JOIN CONTAINER C ON C.CONTAINERID=CCI.CONTAINERID
                left join WORKFLOW W ON W.WORKFLOWID=DECODE(CCI.ISINSTORE,-1,C.MFGWORKFLOWID,CCI.WORKFLOWID)
                LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID=W.WORKFLOWBASEID
                left join containersubcontainer cc on cc.subcontainerid=cci.containerid and rownum=1
                left join T_DDQLT td ON td.AUFNR =cci.MFGORDERNAME
                left join ApsConCompleteSet acc on acc.CONTAINERID=CCI.CONTAINERID and acc.isuse=0
                left join APSPLANINFO ai ON ai.CONTAINERID =CCI.CONTAINERID AND ai.ISUSE =0
                WHERE cci.isinpool=1 and cci.isinstore != 1 AND ai.APSPLANINFOID IS NOT null and ai.ApsType=1 and ai.ApsType2=2
                    ";
}